iT邦幫忙

第 11 屆 iThome 鐵人賽

DAY 4
1
Software Development

從問題理解與活用SQL語法系列 第 4

第四堂:家事管理 - 從計算年紀 理解 SELECT 的常客(日期函式、數學函式、型態轉換)

  • 分享至 

  • xImage
  •  

一、回顧:第三堂 情境練習題

題目:統計各部門的員工薪資總和

https://www.tutorialrepublic.com/codelab.php?topic=sql&file=select-all

依照各部門薪水總和最高到薪水總和最低,依序列出部門員工薪資總和大於10000的部門ID、部門薪資總和。沒有部門編號的員工(dept_id <> 'null') 不包含在資料的統計結果內

實作結果

https://ithelp.ithome.com.tw/upload/images/20190920/20120331Rye1no6pX0.png

SELECT dept_id,
       SUM(salary) AS "薪資總和"
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING SUM(salary) > 10000
ORDER BY "薪資總和"

複習內容:分類後的統計

統計前的分類→統計前後的過濾條件→挑選被統計與沒有被統計的欄位

理解順序1:統計前的分類基準:GROUP BY

FROM employees
GROUP BY dept_id

理解順序2:統計前的資料過濾 WHERE 和 統計後的資料過濾HAVING

WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING SUM(salary) > 10000
ORDER BY "薪資總和"

理解順序3:挑選被統計的欄位(SQL Aggregate Functions)與沒有被統計的欄位

SELECT dept_id,
	   SUM(salary) AS "薪資總和"
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING SUM(salary) > 10000
ORDER BY "薪資總和"

衍伸補充:比較運算子(大於、等於、小於、IS NULL、IS NOT NULL)

在sql的數字比較跟大多數的C語言程式語言相同,不過有兩點需要注意:

(一) 相等 與 不相等
1.相等:= (一個等號)
例如,A欄位是否等於B欄位: A = B

2.不相等:<> (大於小於)
例如,A欄位是否不等於B欄位:A <> B

(二) IS NULL 和 IS NOT NULL
在資料庫系統當中,不是每個欄位都會有值
如果沒有值,在資料庫以「NULL」表示「空值」

1.判斷是空值:IS NULL
例如,A欄位 是否為 空值:A IS NULL

2.判斷是否有值: IS NOT NULL
例如,A 欄位 是否有值:A IS NOT NULL

二、準備資料

DB Fiddle

網址:
https://www.db-fiddle.com/

建立表格與新增資料的DDL SQL

CREATE TABLE Family (
  `FamilyId` VARCHAR(100) PRIMARY KEY,
  `FamilyName` VARCHAR(5),
  `FamilySex` VARCHAR(2),
  `BirthDate` DATETIME,
  `PhoneNumber` VARCHAR(10)
) CHARACTER SET=utf8;

INSERT INTO Family
  (`FamilyId`, `FamilyName`, `FamilySex`, `BirthDate`, `PhoneNumber`)
VALUES
  ('34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '泰肝', '女生', '2007-10-11', 'NULL'),
  ('3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '泰熱', '男生', '2001-12-10', '0934567890'),
  ('91b18f1f-4ef8-4066-97c4-28daea585db5', '泰胖', '女生', '2003-05-13', 'NULL'),
  ('91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '泰瘦', '女生', '1985-07-10', '0944623456'),
  ('bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '泰冷', '男生', '1995-01-23', '0977654258'),
  ('de8cb5db-2061-4d35-a662-ba5f528fadba', '泰賢', '男生', '1977-02-03', '0944589456');

SQL Server 環境:

CREATE TABLE [dbo].[Family](
	[FamilyId] [nvarchar](100) NOT NULL,
	[FamilyName] [nvarchar](10) NULL,
	[FamilySex] [nvarchar](10) NULL,
	[BirthDate] [datetime] NULL,
	[PhoneNumber] [nvarchar](20) NULL,
 CONSTRAINT [PK_Family] PRIMARY KEY CLUSTERED 
(
	[FamilyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'34bf1b6f-191d-40e9-9a8c-3c282e6a700d', N'泰肝', N'女生', CAST(0x000099C400000000 AS DateTime), NULL)
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'3ad93ba4-c799-4a32-ac2e-8abc74dd6375', N'泰熱', N'男生', CAST(0x0000917100000000 AS DateTime), N'0934567890')
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'91b18f1f-4ef8-4066-97c4-28daea585db5', N'泰胖', N'女生', CAST(0x0000937800000000 AS DateTime), NULL)
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'91dcde4b-10b3-421e-ab8e-bb6bc23b4350', N'泰瘦', N'女生', CAST(0x00007A0400000000 AS DateTime), N'0944623456')
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', N'泰冷', N'男生', CAST(0x000087A000000000 AS DateTime), N'0977654258')
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'de8cb5db-2061-4d35-a662-ba5f528fadba', N'泰賢', N'男生', CAST(0x00006DFD00000000 AS DateTime), N'0944589456')

三、問題情境

列出每位家庭成員的「姓名」、「生日」,並根據成員的出生年月日計算並顯示各自的「年齡」。

MySQL 實作結果

SELECT Family.FamilyName AS "姓名",
       CAST(Family.BirthDate AS DATE) AS "生日 (DATE 預設格式)",
       CONCAT(YEAR(Family.BirthDate) , '年' , MONTH(Family.BirthDate) , '月' , DAY(Family.BirthDate), '日')  AS "生日(中文)", 
       FLOOR(DATEDIFF(now(), Family.BirthDate) / 365.25) AS "年齡"
FROM Family;
姓名 生日 (DATE 預設格式) 生日(中文) 年齡
泰肝 2007-10-11 2007年10月11日 11
泰熱 2001-12-10 2001年12月10日 17
泰胖 2003-05-13 2003年5月13日 16
泰瘦 1985-07-10 1985年7月10日 34
泰冷 1995-01-23 1995年1月23日 24
泰賢 1977-02-03 1977年2月3日 42

SQL Server 實作結果

SELECT Family.FamilyName AS "姓名",
       CAST(Family.BirthDate AS DATE) AS "生日 (DATE 預設格式)",
       CONVERT(VARCHAR(11), Family.BirthDate, 111 ) AS "生日 (yyyy/mm/dd)",
       CONCAT(YEAR(Family.BirthDate) , '年' , MONTH(Family.BirthDate) , '月' , DAY(Family.BirthDate), '日')  AS "生日(中文)", 
       FLOOR(DATEDIFF(DAY, Family.BirthDate, GETDATE()) / 365.25) AS "年齡"
FROM Family

https://ithelp.ithome.com.tw/upload/images/20190920/20120331ioBoSfHmJ9.png

年紀計算說明

  1. 首先,使用 DATEDIFF(now(), Family.BirthDate)用來計算「生日」和「當天日期」的相差天數
SELECT DATEDIFF(now(), Family.BirthDate) AS "年齡"
FROM Family;
年齡
4362
6493
5974
12490
9006
15569
  1. 接著,將天數除以365.25 粗估歲數(四年一閏年,所以多出0.25)
SELECT DATEDIFF(now(), Family.BirthDate) / 365.25 AS "年齡"
FROM Family;
年齡
11.948
17.7823
16.3614
34.2012
24.6626
42.6311

  1. 最後,使用FLOOR 將計算出來的小數點捨去掉(還沒過生日的人今年不能算)

Query #1

SELECT FLOOR(DATEDIFF(now(), Family.BirthDate) / 365.25) AS "年齡"
FROM Family;
年齡
11
17
16
34
24
42

四、細節講解

重點一:常用的日期函式類型

手冊速查

  1. SQL Server
    https://docs.microsoft.com/zh-tw/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017
  2. MySQL
    https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html
  3. Oracle
    https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm
  4. postgresql
    https://www.postgresql.org/docs/8.1/functions-datetime.html

對應範例SQL:

(YEAR(Family.BirthDate) + '年' + MONTH(Family.BirthDate) + '月' + DAY(Family.BirthDate)) + '日' AS "生日(中文)", 
       FLOOR(DATEDIFF(DAY, Family.BirthDate, GETDATE()) / 365.25) AS "年齡"

類型1.取得當下日期時間

每個資料庫系統使用的函式都不太一樣

  • SQL Server:GETDATE()
  • MySQL:NOW()、SYSDATE()
  • Oracle DB: SYSDATE 「他不是函式」
  • PostgreSQL:NOW()

類型2.取得某個日期時間的「年」、「月」、「日」

  • DATE_PART()
  • EXTRACT() (相當於SQL Server的Year()、Month()、DAY())

類型3.計算兩個日期的相差時間

  • 非Oracle DB:DATEDIFF()
  • Oracle DB:使用TO_DATE()轉換後相減,例如:
TO_DATE('2000-01-02', 'YYYY-MM-DD') -  TO_DATE('2000-01-01', 'YYYY-MM-DD')

重點二:常用的數值函式

對應範例SQL:

FLOOR(DATEDIFF(DAY, Family.BirthDate, GETDATE()) / 365.25) AS "年齡"
  • CEIL:無條件進位
  • FLOOR:無條件捨去
  • ROUND:四捨五入
  • ABS:取絕對值
  • MOD:餘數除法 (SQL Server 使用 A % B 當作 A 除以 B 的餘數)

重點三:常用的型態轉換函式

  • 非Oracle DB:Cast()、CONVERT()
  • Oracle DB: TO_XXXX() (例如 TO_NUMBER()、TO_DATE())

對應範例SQL:

CAST(Family.BirthDate AS DATE) AS "生日 (DATE 預設格式)",
CONVERT(VARCHAR(11), Family.BirthDate, 111 ) AS "生日 (yyyy/mm/dd)",
(YEAR(Family.BirthDate) + '年' + MONTH(Family.BirthDate) + '月' + DAY(Family.BirthDate)) + '日' AS "生日(中文)"

五、觀念延伸閱讀:運算式與函式

MySQL 超新手入門(4)運算式與函式
http://www.codedata.com.tw/database/mysql-tutorial-4-expression-function/

六、相關情境練習

題目資料庫網址(W3Schoool SQL Server 版):

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_datediff

題目資料庫網址(W3Schoool MySQL 版)

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_datediff

使用資料表:

員工資料表 employees

說明

列出所有員工的「完整姓名」、「出生年月日」,並計算每個人活到2222-11-22的年齡歲數(實歲)

結果

https://ithelp.ithome.com.tw/upload/images/20190921/20120331Vi7N1bGFcv.png


上一篇
第三堂:家事管理 - 分類後統計資料(GROUP BY → HAVING→SELECT、Aggregrate Function)
下一篇
第五堂:家事管理 - 每日家事分工一覽表 (SELECT 子查詢)
系列文
從問題理解與活用SQL語法30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言